Create Dictionary Tables in ODS Database and Populate the Dictionary Tables
12
Download the SQL File attached to the
Run Script that is attached to create your Dictionary tables in the ODS Database. Once ran, the tables will be made up of these columns.
After creating the Dictionary tables in ODS Database, use SSIS to populate the Dictionary tables, create "LoadDictionaryTables" Package
- For each Dictionary Table drag and drop a Data Flow Task in Control Flow
- Inside the Data Flow Task, drag and drop OLE DB Source and add a OLE DB connection manager, use SQL command to select distinct values for dictionary's columns
- Drag and drop Derived Column Transformation for adding new column
- Drag and drop Data Conversion Transformation, the Data Types and Length will match the Destination table Data Types and Length
- Drag and drop a Lookup Transformation and configure it accordingly
- Drag and drop OLE DB Destination and connect it to "Lookup No Match Output" in order to insert new rows in Dictionary table
- Drag and drop another Lookup Transformation and configure it to have existing rows as "Lookup Match Output"
- Drag and drop OLE DB command and connect it to second "lookup Match Output " and configure it to Update existing rows
- Repeat the same steps to load all dictionary tables
DFT- DictionaryIncident
Derived Column "IncidentCategory"
IncidentType == "HIT SHOOTING INCIDENT" ? 1 : IncidentType == "NON-HIT SHOOTING INCIDENT" ? 0 :
DFT- DictionaryLocation
DFT- DictionaryDistrict
DFT- DictionaryRace
DFT- DictionaryHandlingUnit
DFT- DictionaryWeapon
DFT- ShootingIncident
Execute the package to populate the Dictionary tables with the data
The Dictionary tables are populated with Data
